﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using OneSoft.EntityCore;
using OneSoft.DataAccess;
using BySoft.Server.Common.Enum;

using BySoft.Server.Common.Entity;
using BySoft.Server.Common.Schema;
using BySoft.Server.Consts;

namespace BySoft.Server.DataAccess
{
    public partial class SysRoleDA
    {
        public static DataTable GetRoleList(string roleName,int pageIndex, int pageSize, out int count)
        {
            DataAccessParameterCollection parameters = new DataAccessParameterCollection();

            string sSql = "SELECT  RoleID";
            sSql += "," + SysRoleTable.C_ROLENAME;
            sSql += "," + SysRoleTable.C_DESCRIPTION;
            sSql += " FROM " + SysRoleTable.C_TABLENAME;
            sSql += " WHERE " + SysRoleTable.C_STATUS + "='1'";
            if (!string.IsNullOrEmpty(roleName))
            {
                sSql += " AND "+SysRoleTable.C_ROLENAME+" like @roleName";
                parameters.AddWithValue("@roleName","%"+ roleName+"%");
            }
            sSql += " ORDER BY " + SysRoleTable.C_ROLEID;
            return DataAccessCommon.ExecuteDataTablePaging(sSql, pageIndex, pageSize, parameters, out count) ;
        }

        public static DataTable GetRoleListWithoutUserTypeRole(string roleName, int pageIndex, int pageSize, out int count)
        {
            DataAccessParameterCollection parameters = new DataAccessParameterCollection();

            string sSql = "SELECT  RoleID";
            sSql += "," + SysRoleTable.C_ROLENAME;
            sSql += "," + SysRoleTable.C_DESCRIPTION;
            sSql += " FROM " + SysRoleTable.C_TABLENAME;
            sSql += " WHERE " + SysRoleTable.C_STATUS + "='1'";
            sSql += " AND " + SysRoleTable.C_ROLEID + " not in( SELECT RoleID FROM SYS_UserTypeRole )";
            sSql += " AND " + SysRoleTable.C_ROLEID + " <>'" + CommonConsts.Admin_Role_ID+"'";
            if (!string.IsNullOrEmpty(roleName))
            {
                sSql += " AND " + SysRoleTable.C_ROLENAME + " like @roleName";
                parameters.AddWithValue("@roleName", "%" + roleName + "%");
            }
            sSql += " ORDER BY " + SysRoleTable.C_ROLEID;

            return DataAccessCommon.ExecuteDataTablePaging(sSql, pageIndex, pageSize, parameters, out count);
        }

        public static DataTable GetRoleByRoleName(string roleName)
        {
            if (DataAccessCommon.DefaultInstanceConfig.DBType == DataAccessConfiguration.DBTYPE_ORACLE)
            {
                #region Oracle
                DataAccessParameterCollection parameters = new DataAccessParameterCollection();

                string sSql = "SELECT " + SysRoleTable.C_ROLEID;
                sSql += "," + SysRoleTable.C_ROLENAME;
                sSql += "," + SysRoleTable.C_DESCRIPTION;
                sSql += " FROM " + SysRoleTable.C_TABLENAME;
                sSql += " WHERE " + SysRoleTable.C_STATUS + "='1'";
                sSql += " and Upper(" + SysRoleTable.C_ROLENAME + ")=:roleName";

                parameters.AddWithValue(":roleName", roleName.ToUpper());

                return DataAccessCommon.Select(sSql, parameters).Tables[0];
                #endregion
            }
            else
            {
                #region SQL Server
                DataAccessParameterCollection parameters = new DataAccessParameterCollection();

                string sSql = "SELECT " + SysRoleTable.C_ROLEID;
                sSql += "," + SysRoleTable.C_ROLENAME;
                sSql += "," + SysRoleTable.C_DESCRIPTION;
                sSql += " FROM " + SysRoleTable.C_TABLENAME;
                sSql += " WHERE " + SysRoleTable.C_STATUS + "='1'";
                sSql += " and Upper(" + SysRoleTable.C_ROLENAME + ")=@roleName";

                parameters.AddWithValue("@roleName", roleName.ToUpper());

                return DataAccessCommon.Select(sSql, parameters).Tables[0];
                #endregion
            }

        }

        public static DataTable GetMemberForRole(string id, string name, int pageIndex, int pageSize, out int count)
        {

                #region SQL Server
                DataAccessParameterCollection parameters = new DataAccessParameterCollection();
                string sSql = "SELECT * FROM ( SELECT a.UserID AS ID,a.UserloginID AS LOGINID, a.UserName AS NAME ";
                sSql += " FROM sys_user a";
                sSql += " WHERE a.status='1' and a.CanUseRole='1' and a.userid<>'"+CommonConsts.ADMIN_USER+"'";
                sSql += ") T ";
                sSql += " where 1=1 ";
                if (!String.IsNullOrEmpty(id))
                {
                    sSql += " and Lower(LOGINID) like @id";
                    parameters.AddWithValue("@id", "%" + id + "%");
                }
                if (!String.IsNullOrEmpty(name))
                {
                    sSql += " and Lower(NAME) like @name";
                    parameters.AddWithValue("@name", "%" + name + "%");
                }
                sSql += " order by LOGINID ";
                return DataAccessCommon.ExecuteDataTablePaging( sSql, pageIndex, pageSize, parameters, out  count);
                #endregion

        }

        public static DataTable GetRoleMember(string roleId)
        {
            #region SQL Server
            DataAccessParameterCollection parameters = new DataAccessParameterCollection();
            string sSql = " SELECT c.UserRoleID, a.userid as ID,a.UserloginID as DISPLAY_ID, a.username AS NAME ";
            sSql += " FROM sys_user a,SYS_UserRole c ";
            sSql += " WHERE ";
            sSql += " a.userid= c.userid";
            sSql += " AND c.roleid = @roleId";
            parameters.AddWithValue("@roleId", roleId);

            return DataAccessCommon.Select(sSql, parameters).Tables[0];
            #endregion
        }

        public static int DeleteUserRole(string roleId, DataAccessBroker broker)
        {

            if (broker.Configuration.DBType == DataAccessConfiguration.DBTYPE_ORACLE)
            {
                #region Oracle
                DataAccessParameterCollection parameters = new DataAccessParameterCollection();
                string sSql = " DELETE FROM " + SysUserroleTable.C_TABLENAME;
                sSql += " WHERE " + SysUserroleTable.C_ROLEID + "=:roleId";

                parameters.AddWithValue(":roleId", roleId);

                return broker.ExecuteSQL(sSql, parameters);
                #endregion
            }
            else
            {
                #region SQL Server
                DataAccessParameterCollection parameters = new DataAccessParameterCollection();
                string sSql = " DELETE FROM " + SysUserroleTable.C_TABLENAME;
                sSql += " WHERE " + SysUserroleTable.C_ROLEID + "=@roleId";

                parameters.AddWithValue("@roleId", roleId);

                return broker.ExecuteSQL(sSql, parameters);
                #endregion
            }
        }





        public static int DeleteRoleRights(string roleId)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                if (broker.Configuration.DBType == DataAccessConfiguration.DBTYPE_ORACLE)
                {
                    #region Oracle
                    DataAccessParameterCollection parameters = new DataAccessParameterCollection();
                    string sSql = " DELETE FROM " + SysRolerightsTable.C_TABLENAME;
                    sSql += " WHERE " + SysRolerightsTable.C_ROLEID + "=:roleId";

                    parameters.AddWithValue(":roleId", roleId);

                    return broker.ExecuteSQL(sSql, parameters);
                    #endregion
                }
                else
                {
                    #region SQL Server
                    DataAccessParameterCollection parameters = new DataAccessParameterCollection();
                    string sSql = " DELETE FROM " + SysRolerightsTable.C_TABLENAME;
                    sSql += " WHERE " + SysRolerightsTable.C_ROLEID + "=@roleId";

                    parameters.AddWithValue("@roleId", roleId);

                    return broker.ExecuteSQL(sSql, parameters);
                    #endregion
                }

            }
        }

        public static int DeleteRoleRights(string roleId, DataAccessBroker broker)
        {
            if (broker.Configuration.DBType == DataAccessConfiguration.DBTYPE_ORACLE)
            {
                #region Oracle
                DataAccessParameterCollection parameters = new DataAccessParameterCollection();
                string sSql = " DELETE FROM " + SysRolerightsTable.C_TABLENAME;
                sSql += " WHERE " + SysRolerightsTable.C_ROLEID + "=:roleId";

                parameters.AddWithValue(":roleId", roleId);
                return broker.ExecuteSQL(sSql, parameters);
                #endregion
            }
            else
            {
                #region SQL Server
                DataAccessParameterCollection parameters = new DataAccessParameterCollection();
                string sSql = " DELETE FROM " + SysRolerightsTable.C_TABLENAME;
                sSql += " WHERE " + SysRolerightsTable.C_ROLEID + "=@roleId";

                parameters.AddWithValue("@roleId", roleId);
                return broker.ExecuteSQL(sSql, parameters);
                #endregion
            }

        }


        public static SysRoleExEntity GetSysRoleExEntity(string roleId)
        {
            if (DataAccessCommon.DefaultInstanceConfig.DBType == DataAccessConfiguration.DBTYPE_ORACLE)
            {
                #region Oracle
                DataAccessParameterCollection parameters = new DataAccessParameterCollection();
                string sSql = " SELECT A.*,B." + SysUserTable.C_USERNAME + " AS Createusername";
                sSql += ",C." + SysUserTable.C_USERNAME + " AS Modifyusername";
                sSql += " FROM " + SysRoleTable.C_TABLENAME + " A,";
                sSql += SysUserTable.C_TABLENAME + " B," + SysUserTable.C_TABLENAME + " C ";
                sSql += " WHERE A." + SysRoleTable.C_CREATEUSERID + "= B." + SysUserTable.C_USERID;
                sSql += " AND A." + SysRoleTable.C_MODIFYUSERID + "= C." + SysUserTable.C_USERID;
                sSql += " AND A." + SysRoleTable.C_ROLEID + "=:roleId";
                parameters.AddWithValue(":roleId", roleId);

                return DataAccessCommon.SelectSingle<SysRoleExEntity>(sSql, parameters);
                #endregion
            }
            else
            {
                #region SQL Server
                DataAccessParameterCollection parameters = new DataAccessParameterCollection();
                string sSql = " SELECT A.*,B." + SysUserTable.C_USERNAME + " AS Createusername";
                sSql += ",C." + SysUserTable.C_USERNAME + " AS Modifyusername";
                sSql += " FROM " + SysRoleTable.C_TABLENAME + " A ";
                sSql += " left join " + SysUserTable.C_TABLENAME + " B ";
                sSql += " on A." + SysRoleTable.C_CREATEUSERID + "= B." + SysUserTable.C_USERID;
                sSql += " left join " + SysUserTable.C_TABLENAME + " C ";
                sSql += " on A." + SysRoleTable.C_MODIFYUSERID + "= C." + SysUserTable.C_USERID;
                sSql += " where A." + SysRoleTable.C_ROLEID + "=@roleId";
                parameters.AddWithValue("@roleId", roleId);

                return DataAccessCommon.SelectSingle<SysRoleExEntity>(sSql, parameters);
                #endregion
            }
        }

        public static bool IsContainUser(string roleId)
        {
            String sqlStr = " SELECT COUNT(UserRoleID) FROM SYS_UserRole WHERE RoleID=@RoleID ";
            DataAccessParameterCollection dpc = new DataAccessParameterCollection();
            dpc.AddWithValue("@RoleID", roleId);

            if (Convert.ToInt32(DataAccessCommon.SelectSingleObject(sqlStr, dpc)) > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static bool HavePower(string strUserId,string strRoleName)
        {
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    #region SQL Server
                    string strcmd = @"SELECT * FROM SYS_Role a
LEFT JOIN SYS_UserRole b ON b.RoleID=a.RoleID
WHERE b.UserID=@UserID AND a.RoleName=@RoleName";
                    DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                    dpc.AddWithValue("@UserID", strUserId);
                    dpc.AddWithValue("@RoleName", strRoleName);
                    object obj = broker.ExecuteSQLScalar(strcmd, dpc);
                    if (obj == null || string.IsNullOrEmpty(obj.ToString()))
                    {
                        return false;
                    }
                    else
                    {
                        return true;
                    }
                    #endregion
                }
                catch
                {
                    throw;
                }
            }
        }
    }
}
